package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.data.repositories.TimedTaskRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by ThinkPad on 2016/11/3.
 */
@Repository
public class TimedTaskRepositoryImpl extends BaseRepository implements TimedTaskRepository {

    @Override
    public List getSendMsgListTask开票提醒() {
        //给调入经销商财务发送消息
        String sql = "SELECT c.*, a.custom_tel, a.invoice_issuing_date,a.expectedLateMoneyInAccount,a.out_confirm_date,a.fund_out_date FROM car_transfer_log a \n" +
                "JOIN lsh库存表 c ON a.stock_code = c.库存编号 \n" +
                "JOIN `itask基础设置` b ON c.`调出经销商` = b.`许可` \n" +
                "JOIN message_setting d ON d.permit = c.`调出经销商` AND d.type = '2' AND b.设置项目 = '调车_调车确认后开票提醒' \n" +
                "WHERE a.out_confirm_date IS NOT NULL AND a.invoice_issuing_date IS NULL AND ( out_confirm_date <= ADDDATE(NOW(),(1 - b.`值`)))";
        return this.getList(sql);
    }

    @Override
    public List getSendMsgListTask付款提醒() {
        //给调出经销商财务发送消息
        String sql = "SELECT c.*, a.custom_Tel, a.invoice_issuing_date,a.expectedLateMoneyInAccount,a.fund_out_date FROM car_transfer_log a \n" +
                "JOIN lsh库存表 c ON a.stock_code = c.库存编号 \n" +
                "JOIN `itask基础设置` b ON c.调出经销商 = b.`许可` \n" +
                "JOIN message_setting d ON d.permit = c.`调入经销商` AND d.type = '2' AND b.设置项目 = '调车_开增票后付款提醒' \n" +
                "WHERE a.invoice_issuing_date IS NOT NULL AND a.actual_payment_date IS NULL AND ( invoice_issuing_date <= ADDDATE(NOW(),(1 - b.`值`)))";
        return this.getList(sql);
    }

    @Override
    public List getSendMsgListTask收款提醒() {
        //给调出经销商财务发送消息
        String sql = "SELECT c.*, a.custom_Tel, a.invoice_issuing_date,a.expectedLateMoneyInAccount,a.fund_out_date FROM car_transfer_log a \n" +
                "JOIN lsh库存表 c ON a.stock_code = c.库存编号 \n" +
                "JOIN `itask基础设置` b ON c.调入经销商 = b.`许可` \n" +
                "JOIN message_setting d ON d.permit = c.`调入经销商` AND d.type = '4' AND b.设置项目 = '调车_车辆出库后收款提醒' \n" +
                "WHERE a.fund_out_date IS NOT NULL AND a.fund_in_date IS NULL AND ( stock_out_date <= ADDDATE(NOW(),(1 - b.`值`)))";
        return this.getList(sql);
    }

    @Override
    public List getActualFinanceServeFree() {
        StringBuffer sb = new StringBuffer();
        sb.append(" select b.`许可` permit , IFNULL(SUM(c.`金融服务费`),0) / IFNULL(SUM(c.`贷款额`),1) * 100  actualValue ");
        sb.append(" from vw_retailed_quotation b left join 报价金融表 c on b.`金融` = c.`金融编号`, LSH车型 e    ");
        sb.append(" where b.是否驳回 = '否'   ");
        sb.append(" 	and b.车辆零售状态 = '已卖'   ");
        sb.append(" 	and b.`开票时间` >= date_add(curdate(), interval - day(curdate()) + 1 day) and b.开票时间 <= last_day(curdate())   ");
        sb.append(" 	and b.退车日期 is null     ");
        sb.append(" 	and b.车型编号 = e.车型编号  ");
        sb.append(" group by b.许可 ");
        return this.getList(sb.toString());
    }

    @Override
    public int insertTDefeatLog() {
        String sql = " INSERT INTO t_defeat_log ( " +
                " cust_id, cust_id_state, permit, cust_name, " +
                " cust_phone, cust_sex, interest_rank,\tuser_name, " +
                " defeat_type, source, guard_bank, " +
                " interest_car, for_filing_date " +
                " ) " +
                " SELECT " +
                " a.顾客编号, 0, a.许可, a.顾客姓名, a.电话号码, " +
                " a.顾客性别, a.新顾客级别, a.所属销售顾问, 2, " +
                " a.顾客渠道, a.保有品牌, a.意向车型A, a.留档日期 " +
                " FROM " +
                " `顾客记录汇总` a " +
                " WHERE (a.`顾客状态` != '战败' OR ISNULL(a.`顾客状态`)) " +
                "  AND CusStatus ( " +
                "   `顾客渠道`, `是否交车`, " +
                "   `是否订单`, `上次进店日期`, " +
                "   `顾客状态`, 6 , `新顾客级别` " +
                " ) = '战败' ";
        return this.executeUpdate(sql, null);
    }

    @Override
    public int updateClueStateAndDate() {
        String sql = " update `顾客记录汇总` set " +
                " `CRM战败确认` = IF((顾客状态 <> '战败' OR 顾客状态 IS NULL) AND CusStatus(`顾客渠道`,`是否交车`,`是否订单`,`上次进店日期`,`顾客状态`,6,新顾客级别) = '战败',NULL, CRM战败确认), " +
                " `顾客状态` = CusStatus(`顾客渠道`,`是否交车`,`是否订单`,`上次进店日期`,`顾客状态`,6,新顾客级别), " +
                " `战败日期` = IF(顾客状态 = '战败' AND 战败日期 IS NULL,CURDATE(), 战败日期) ";
        return this.executeUpdate(sql, null);
    }

    @Override
    public List findClueCountInfo() {
        String sql = " select * from (SELECT " +
                "   a.`许可` 许可, " +
                "   IFNULL(a.`意向车型A`,0) 车型编号, " +
                "   a.`顾客状态` 渠道, " +
                "   a.`所属销售顾问` 销售顾问, " +
                "   COUNT(a.顾客状态) 有效线索数, " +
                "   date_format(curdate(),'%Y%m') 周期 " +
                " FROM 顾客记录汇总 a " +
                " WHERE a.顾客状态 IN ('活跃', '休眠') " +
                " GROUP BY a.`许可`, 车型编号, a.`顾客状态`, a.`所属销售顾问`, `周期`) a ";
        return this.getList(sql);
    }
}
